﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Xml;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class PolicyAssertion
    {
        public PolicyAssertion()
        {

        }

        public static Guid CreatePolicyAssertion(DTO.PolicyAssertion policyAssertion, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreatePolicyAssertion]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter AssertionTypeIDParam = new SqlParameter("@AssertionTypeID", SqlDbType.UniqueIdentifier);
                    SqlParameter AssertionParam = new SqlParameter("@Assertion", SqlDbType.Xml);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = policyAssertion.Name;
                    DescriptionParam.Value = policyAssertion.Description;
                    AssertionTypeIDParam.Value = policyAssertion.AssertionType.ID;
                    AssertionParam.Value = policyAssertion.Assertion.OuterXml;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(AssertionTypeIDParam);
                    cmd.Parameters.Add(AssertionParam);

                    cmd.ExecuteNonQuery();
                    policyAssertion.ID = (Guid)IDParam.Value;
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }


            return policyAssertion.ID;
        }

        public static void DeletePolicyAssertion(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeletePolicyAssertion]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static DTO.PolicyAssertion UpdatePolicyAssertion(DTO.PolicyAssertion policyAssertion, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdatePolicyAssertion]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter AssertionTypeIDParam = new SqlParameter("@AssertionTypeID", SqlDbType.UniqueIdentifier);
                    SqlParameter AssertionParam = new SqlParameter("@Assertion", SqlDbType.Xml);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = policyAssertion.ID;
                    NameParam.Value = policyAssertion.Name;
                    DescriptionParam.Value = policyAssertion.Description;
                    AssertionTypeIDParam.Value = policyAssertion.AssertionType.ID;
                    AssertionParam.Value = policyAssertion.Assertion.OuterXml;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(AssertionTypeIDParam);
                    cmd.Parameters.Add(AssertionParam);


                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return policyAssertion;
        }

        public static DTO.PolicyAssertion GetPolicyAssertion(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.PolicyAssertion policyAssertion = new DTO.PolicyAssertion();
            const string sprocname = "[ServiceEngine].[GetPolicyAssertion]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            policyAssertion.ID = reader.GetGuid(0);
                            policyAssertion.Name = reader.GetString(1);
                            policyAssertion.AssertionType = AssertionType.GetAssertionType(reader.GetGuid(2), txn, conn);
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.Load(reader.GetSqlXml(3).CreateReader());
                            policyAssertion.Assertion = xdoc.DocumentElement;
                            if (reader.IsDBNull(4))
                            {
                                policyAssertion.Description = null;
                            }
                            else
                            {
                                policyAssertion.Description = reader.GetString(4);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return policyAssertion;

        }

        public static DTO.PolicyAssertionList ListPolicyAssertions(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.PolicyAssertionList list = new DTO.PolicyAssertionList();
            const string sprocname = "[ServiceEngine].[ListPolicyAssertions]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.PolicyAssertions = new List<DTO.PolicyAssertionListElement>();
                            while (reader.Read())
                            {
                                DTO.PolicyAssertionListElement element = new DTO.PolicyAssertionListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.PolicyAssertions.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.PolicyAssertionList ListPolicyAssertionsByPolicy(Guid policyID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.PolicyAssertionList list = new DTO.PolicyAssertionList();
            const string sprocname = "[ServiceEngine].[ListPolicyAssertionsByPolicy]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter IDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = policyID;
                    cmd.Parameters.Add(IDParam);


                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.PolicyAssertions = new List<DTO.PolicyAssertionListElement>();
                            while (reader.Read())
                            {
                                DTO.PolicyAssertionListElement element = new DTO.PolicyAssertionListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.PolicyAssertions.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.PolicyAssertionListElement> ListPolicyAssertionsByGroup(Guid entityGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.PolicyAssertionListElement> list = new List<DTO.PolicyAssertionListElement>();
            const string sprocname = "[ServiceEngine].[ListEntitiesByGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = entityGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.PolicyAssertionListElement>();
                            while (reader.Read())
                            {
                                DTO.PolicyAssertionListElement element = new DTO.PolicyAssertionListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
            return list;

        }

    }
}
